﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;

namespace DAL
{
  public  class EmpRewardsDao
    {

        #region 奖惩
        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }


        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }

        public bool DeleteOneInfo(string rewardsID)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Admin_Emp_Rewards Where RewardsID=" + rewardsID);
        }
        //获取一条记录
        public DataTable GetOneInfo(string rewardsID)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from Admin_Emp_Rewards Where RewardsID=" + rewardsID);
        }
        public DataTable GetLists(string deptID, string serWord, string bTime, string eTime, string typeID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(GetSQL(deptID, serWord, bTime, eTime, typeID));
            return dt;
        }
        public DataTable GetLists(string deptID, string serWord, string bTime, string eTime, string typeID, string Export)
        {
            StringBuilder strSQL = new StringBuilder("select RewardsTime as 奖惩时间,DeptName as 部门,EmpCode as 员工工号,EmpName as 员工姓名,DutyName as 职务,StateName as 在职状态,RewardsReason as 事由,ReJ as 奖励类型,ReA as 奖励金额,WaJ as 惩罚类型,WaA as 惩罚金额,Memo as 备注 from ");
            strSQL.Append(" (select * from ");
            strSQL.Append(" (select RewardsID,EmpCode,EmpName ,DeptName,DutyName ,StateName,RewardsTime ,RewardsTypeID, ");
            strSQL.Append(" RewardsTypeName as ReJ,cast(RewardsAmount as decimal(8,2)) as ReA,Unit as ReU,null as WaJ,null as WaA,null as WaU,RewardsReason,Account,InputTime,Memo,TypeName ");
            strSQL.Append("    from View_EmpRewards where TypeName='奖励' ) b ");
            strSQL.Append(" union all ");
            strSQL.Append(" select * from ");
            strSQL.Append(" (select  RewardsID,EmpCode,EmpName ,DeptName,DutyName ,StateName,RewardsTime ,RewardsTypeID, ");
            strSQL.Append(" null as ReJ,null as ReA,null as ReU,RewardsTypeName as WaJ,cast(RewardsAmount as decimal(8,2)) as WaA,Unit as WaU,RewardsReason,Account,InputTime,Memo,TypeName ");
            strSQL.Append("  from View_EmpRewards where TypeName='惩罚' ) c) a Where 1=1 ");

            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (a.EmpCode='" + serWord + "' Or a.EmpName Like '%" + serWord + "%')");
            else
            {
                if (deptID != "" && deptID != "0")
                    strSQL.Append(" And a.DeptName like '%" + deptID + "%'");
                if (typeID != "" && typeID != "0")
                {
                    if (typeID == "30000")
                    {
                        strSQL.Append(" And a.RewardsTypeID in (Select ID From Admin_Rewards_Type Where TypeName='奖励') ");
                    }
                    else if (typeID == "30001")
                    {
                        strSQL.Append(" And a.RewardsTypeID in (Select ID From Admin_Rewards_Type Where TypeName='惩罚') ");
                    }
                    else
                        strSQL.Append(" And a.RewardsTypeID =" + typeID);
                }
                if (RegexRule.IsDate(bTime))
                    strSQL.Append(" And a.RewardsTime>='" + bTime + "'");
                if (RegexRule.IsDate(eTime))
                    strSQL.Append(" And a.RewardsTime<='" + eTime + "'");
            }
            strSQL.Append(" Order by a.InputTime DESC");
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
            return dt;
        }
        //SQL
        public string GetSQL(string deptID, string serWord, string bTime, string eTime, string typeID)
        {
            // StringBuilder strSQL = new StringBuilder("Select a.*,b.Unit From Admin_Emp_Rewards a , Admin_Rewards_Type  b where b.ID=a.RewardsTypeID ");
            StringBuilder strSQL = new StringBuilder("select * from ");
            strSQL.Append(" (select * from ");
            strSQL.Append(" (select RewardsID,EmpCode,EmpName ,DeptName,DutyName ,StateName,RewardsTime ,RewardsTypeID, ");
            strSQL.Append(" RewardsTypeName as ReJ,cast(RewardsAmount as decimal(8,2)) as ReA,Unit as ReU,null as WaJ,null as WaA,null as WaU,RewardsReason,Account,InputTime,Memo,TypeName ");
            strSQL.Append("    from View_EmpRewards where TypeName='奖励' ) b ");
            strSQL.Append(" union all ");
            strSQL.Append(" select * from ");
            strSQL.Append(" (select  RewardsID,EmpCode,EmpName ,DeptName,DutyName ,StateName,RewardsTime ,RewardsTypeID, ");
            strSQL.Append(" null as ReJ,null as ReA,null as ReU,RewardsTypeName as WaJ,cast(RewardsAmount as decimal(8,2)) as WaA,Unit as WaU,RewardsReason,Account,InputTime,Memo,TypeName ");
            strSQL.Append("  from View_EmpRewards where TypeName='惩罚' ) c) a Where 1=1 ");

            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (a.EmpCode='" + serWord + "' Or a.EmpName Like '%" + serWord + "%')");
            else
            {
                if (deptID != "" && deptID != "0")
                    strSQL.Append(" And a.DeptName like '%" + deptID + "%'");
                if (typeID != "" && typeID != "0")
                {
                    if (typeID == "30000")
                    {
                        strSQL.Append(" And a.RewardsTypeID in (Select ID From Admin_Rewards_Type Where TypeName='奖励') ");
                    }
                    else if (typeID == "30001")
                    {
                        strSQL.Append(" And a.RewardsTypeID in (Select ID From Admin_Rewards_Type Where TypeName='惩罚') ");
                    }
                    else
                        strSQL.Append(" And a.RewardsTypeID =" + typeID);
                }
                if (RegexRule.IsDate(bTime))
                    strSQL.Append(" And a.RewardsTime>='" + bTime + "'");
                if (RegexRule.IsDate(eTime))
                    strSQL.Append(" And a.RewardsTime<='" + eTime + "'");
            }
            strSQL.Append(" Order by a.InputTime DESC");
            //strSQL.Append(" Order by a.DeptName,DutyName,a.RewardsTime");
            return strSQL.ToString();
        }


        public DataTable GetStatisticByEmp(string typeName, string bTime, string eTime)
        {
            string strSQL = "select top 500 EmpName as 姓名,sum(RewardsAmount) as 合计金额 from Admin_Emp_Rewards where 1=1";
            if (typeName == "奖励")
                strSQL += " AND RewardsAmount>0 ";
            else if (typeName == "惩罚")
                strSQL += " AND RewardsAmount<0 ";
            else
            {
                strSQL = "select top 500 EmpName 姓名,sum(case when RewardsAmount>0 then RewardsAmount else 0 end ) 奖励 ,sum(case when RewardsAmount<0 then RewardsAmount else 0 end ) 惩罚,sum(RewardsAmount) 合计金额 from Admin_Emp_Rewards  where 1=1 ";
            }
            if (RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
                strSQL += " And (RewardsTime>='" + bTime + "' And RewardsTime<='" + eTime + "')";
            else if (RegexRule.IsDate(bTime) && !RegexRule.IsDate(eTime))
                strSQL += " And RewardsTime>='" + bTime + "'";
            else if (!RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
            { //按年，统计12个月份的数据
                DateTime dt = DateTime.Parse(eTime);
                int y = dt.Year;

                StringBuilder sb = new StringBuilder("select top 200 EmpName as 姓名,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='1' then RewardsAmount else 0 end ) '1月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='2' then RewardsAmount else 0 end ) '2月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='3' then RewardsAmount else 0 end ) '3月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='4' then RewardsAmount else 0 end ) '4月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='5' then RewardsAmount else 0 end ) '5月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='6' then RewardsAmount else 0 end ) '6月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='7' then RewardsAmount else 0 end ) '7月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='8' then RewardsAmount else 0 end ) '8月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='9' then RewardsAmount else 0 end ) '9月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='10' then RewardsAmount else 0 end ) '10月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='11' then RewardsAmount else 0 end ) '11月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='12' then RewardsAmount else 0 end ) '12月' ,");
                sb.Append("sum(RewardsAmount) 合计金额  from Admin_Emp_Rewards  where substring(RewardsTime,1,4)=");

                sb.Append("'" + y + "'");
                strSQL = sb.ToString();
            }

            strSQL += " group by  EmpName order by 合计金额 Desc";

            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }

        public DataTable GetStatisticByDept(string typeName, string bTime, string eTime)
        {
            string strSQL = "select DeptName as 部门,sum(RewardsAmount) as 金额 from Admin_Emp_Rewards where 1=1";
            if (RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
                strSQL += " And (RewardsTime>='" + bTime + "' And RewardsTime<='" + eTime + "')";
            else if (RegexRule.IsDate(bTime) && !RegexRule.IsDate(eTime))
                strSQL += " And RewardsTime>='" + bTime + "'";
            else if (!RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
            { //按年，统计12个月份的数据
                DateTime dt = DateTime.Parse(eTime);
                int y = dt.Year;

                StringBuilder sb = new StringBuilder("select DeptName as 部门,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='1' then RewardsAmount else 0 end ) '1月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='2' then RewardsAmount else 0 end ) '2月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='3' then RewardsAmount else 0 end ) '3月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='4' then RewardsAmount else 0 end ) '4月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='5' then RewardsAmount else 0 end ) '5月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='6' then RewardsAmount else 0 end ) '6月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='7' then RewardsAmount else 0 end ) '7月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='8' then RewardsAmount else 0 end ) '8月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='9' then RewardsAmount else 0 end ) '9月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='10' then RewardsAmount else 0 end ) '10月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='11' then RewardsAmount else 0 end ) '11月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='12' then RewardsAmount else 0 end ) '12月' ,");
                sb.Append("sum(RewardsAmount) 合计金额  from Admin_Emp_Rewards  where substring(RewardsTime,1,4)=");

                sb.Append("'" + y + "'");
                strSQL = sb.ToString();
            }
            if (typeName == "奖励")
                strSQL += " AND RewardsAmount>0 ";
            else if (typeName == "惩罚")
                strSQL += " AND RewardsAmount<0 ";
            strSQL += " group by  DeptName order by 金额 Desc";

            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }

        #endregion
    }
}
